using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DataAccessLibrary
{
	///(1)While Inserting into SQL Database Use [ convert(datetime,'21-02-2006',105)  ] for
	///   Inserting dd-MM-yyy format date Input.
	///(2)While Quering SQL Database Use [   convert(char(11),holidaydate,105)   ] for
	///   fetching data in dd-MM-yyyy format ( remember when using it in where clause you have to use single codes to the date.
	///These 2 method are very necessary and you don't have to think about which date format is active in database.
	/// <summary>
	/// Summary description for Sql.
	/// </summary>
	public class SqlDataClient : IDisposable
	{

		private SqlConnection Con;
		private SqlCommand Cmd ; 
		private  SqlDataReader DR;
		private  SqlDataAdapter Adp  ;
		private  IDbTransaction Tran ;
		private  string ConnectionString ;
		public SqlDataClient(string ConnStr)
		{
			ConnectionString = ConnStr;		
		}
		public  void ConnectToDB()
		{
			try
			{
				if (Con == null) Con = new SqlConnection(ConnectionString);
				if (Con.State != ConnectionState.Open ) Con.Open();
			}
			catch (Exception ex)
			{
				throw new Exception("Unble to connct to the Database" + ex.Message);
			}
			
		}
		private  void CloseDB()
		{
			if ( Con != null )
			{
				if (Con.State != ConnectionState.Closed ) Con.Close();
				Con = null;
				Con.Dispose();				
			}
		}
		public void Dispose()
		{
			CloseDB();			
			if (DR != null) DR=null;
			if (Adp != null) Adp.Dispose();
			if (Tran != null) Tran.Dispose();
		}	
		public void  StartTran()
		{
			ConnectToDB();
			Cmd=new SqlCommand();
			Tran = Con.BeginTransaction(IsolationLevel.ReadCommitted);
			
		}
		public  void RollBackTran()
		{
			Tran.Rollback();
			CloseDB();
		}
		public  void CommitTran()
		{
			Tran.Commit();
			CloseDB();
		}
		public  bool ExecuteNonQuery(string DML)
		{
			try
			{
				ConnectToDB();
				if (Cmd==null) Cmd=new SqlCommand();
				Cmd.CommandText = DML;
				Cmd.Connection = Con;
				Cmd.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex )
			{
				throw new ApplicationException(ex.Message);
				
			}
			finally
			{				
				//Cmd.Dispose();				
			}
		}	
		
        
		public DataTable GetDataTable(string SQL , string TableName )
		{
			DataTable DT =new DataTable();
			ConnectToDB();
			SqlCommand tmpCmd = new SqlCommand(SQL);
			try
			{				
				
				tmpCmd.Connection = Con;
				Adp = new SqlDataAdapter(tmpCmd);
				Adp.Fill(DT);
				DT.TableName=TableName;
				Adp.Dispose();
				return DT.Copy();
			}
			catch (Exception ex)
			{
				throw new ArgumentException(ex.Message);
			}
			finally
			{
				if (DT != null) DT.Dispose();
				if (tmpCmd != null) tmpCmd.Dispose();
				if (Adp != null) Adp.Dispose();
			}
		}
		public SqlDataReader GetReadedDataRow(string SQL )
		{
			ConnectToDB();
			SqlCommand tmpCmd = new SqlCommand(SQL);
			try
			{				
				tmpCmd.Connection = Con;
				DR = Cmd.ExecuteReader();
				return DR;
			}
			catch (Exception ex)
			{
				throw new ArgumentException(ex.Message);
			}
			finally
			{
				if (tmpCmd != null) tmpCmd.Dispose();
				if (DR != null)DR=null;
			}
		}
	
		public  void executeProcedure(string procedureName )
		{
			ConnectToDB();
			SqlCommand tmpCmd = new SqlCommand(procedureName);
			try
			{
						
				tmpCmd.Connection = Con;
				tmpCmd.CommandType = CommandType.StoredProcedure;
				tmpCmd.ExecuteReader();
			}
			catch (Exception ex)
			{
				throw new ArgumentException(ex.Message);
			}
			finally
			{
				if (tmpCmd != null) tmpCmd.Dispose();
				
			}
		}


		public string GetValue(string SingleValueSQL) 
		{
			ConnectToDB();
			SqlCommand tmpCmd = new SqlCommand(SingleValueSQL);
			try
			{
				
				object str;				
				tmpCmd.Connection = Con;
				str = tmpCmd.ExecuteScalar();
				if (str == null)
				{
					return  string.Empty;
				}
				else
				{
					return str.ToString();
				}
			}
			catch (Exception ex)
			{
				throw new ArgumentException(ex.Message);
			}
			finally
			{
				if (tmpCmd != null) tmpCmd.Dispose();
				
			}
		}	
//////////////////		public string FormatDateSql(object DtInDMYFormat)	
//////////////////		{
//////////////////			if (DtInDMYFormat == null)  return " Null ";
//////////////////			string Str=DtInDMYFormat.ToString();
//////////////////			if (Str.Trim()== "")  return " Null ";
//////////////////			Str=Str.Replace(@"/","-");
//////////////////			Str=Str.Replace(@"\","-");
//////////////////			string[] STR;
//////////////////			STR=Str.Split('-');
//////////////////			Str= STR[1] + "-" + STR[0] + "-" + STR[2].Substring(0,4);
//////////////////			return "'" + Str + "'" ;
//////////////////			return System.Convert.ToString(CommonFunction.FormatDateSQL(Str));
//////////////////			
//////////////////		}
		public string GetServerDate()	
		{
			string Str;			
			Str=GetValue("Select convert(char(11),getdate()) ToDay ");
			Str="'" + Str.Substring(0,10)+ "'";
			return Str ;
		}
	}
}